#include <odb/database.hxx>
#include <odb/mysql/database.hxx>
#include "student.hxx"
#include "student-odb.hxx"
#include <gflags/gflags.h>

DEFINE_string(host, "127.0.0.1", "这是MySQL服务器地址");
DEFINE_int32(port, 0, "这是MySQL服务器端口");
DEFINE_string(db, "TestDB", "数据库默认库名称");
DEFINE_string(user, "root", "MySQL用户名称");
DEFINE_string(password, "Czh432248", "MySQL用户密码");
DEFINE_string(cest, "utf8", "MySQL客户端字符集");
DEFINE_int32(max_num, 3, "MySQL连接池最大连接数量");

// 新增班级
void insert_class(odb::mysql::database &db)
{
    try
    {
        // 3.获取事务对象，开启事务
        odb::transaction transaction(db.begin());
        Classes c1("高三一班");
        Classes c2("高三二班");
        db.persist(c1);
        db.persist(c2);
        // 4.提交事务
        transaction.commit();
    }
    catch (std::exception &e)
    {
        std::cout << "插入班级数据出现异常，原因[" << e.what() << "]" << std::endl;
    }
}
// 新增学生
void insert_student(odb::mysql::database &db)
{
    try
    {
        // 3.获取事务对象，开启事务
        odb::transaction transaction(db.begin());
        Student s1(2022044026, "陈志鸿", 22, 01);
        Student s2(2024044026, "钟", 18, 01);
        Student s3(2023044026, "咖啡哥", 19, 02);
        db.persist(s1);
        db.persist(s2);
        db.persist(s3);
        // 4.提交事务
        transaction.commit();
    }
    catch (std::exception &e)
    {
        std::cout << "插入学生数据出现异常，原因[" << e.what() << "]" << std::endl;
    }
}

// 多表联查
void select_class_student(odb::mysql::database &db, const std::string &class_name)
{
    try
    {
        // 3.获取事务对象，开启事务
        odb::transaction transaction(db.begin());
        using query = odb::query<classes_student>;
        using result = odb::result<classes_student>;
        // query::name == stu_name 会组织为一个SQL语句
        result ret(db.query<classes_student>(query::classes::name == class_name));
        // 多表查询后对数据的操作
        for (auto it = ret.begin(); it != ret.end(); ++it)
        {
            std::cout << it->sn << std::endl;
            std::cout << it->name << std::endl;
            std::cout << *it->age << std::endl;
            // std::cout << it->classes_id << std::endl;
            std::cout << it->classes_name << std::endl;
        }
        // 4.提交事务
        transaction.commit();
    }
    catch (std::exception &e)
    {
        std::cout << "多表查询出现异常，原因[" << e.what() << "]" << std::endl;
    }
}
// 查询所有学生姓名
void select_all_student_name(odb::mysql::database &db)
{
    try
    {
        // 3.获取事务对象，开启事务
        odb::transaction transaction(db.begin());
        using query = odb::query<all_name>;
        using result = odb::result<all_name>;
        // query::name == stu_name 会组织为一个SQL语句
        result ret(db.query<all_name>());
        // 多表查询后对数据的操作
        for (auto it = ret.begin(); it != ret.end(); ++it)
        {
            std::cout<<it->name<<std::endl;
        }
        // 4.提交事务
        transaction.commit();
    }
    catch (std::exception &e)
    {
        std::cout << "多表查询出现异常，原因[" << e.what() << "]" << std::endl;
    }
}
// 通过过滤条件来查询所有满足学生姓名---这里为班级名称
void select_query_student_name(odb::mysql::database &db, const std::string &class_name)
{
    try
    {
        // 3.获取事务对象，开启事务
        odb::transaction transaction(db.begin());
        using query = odb::query<all_name>;
        using result = odb::result<all_name>;
        // query::name == stu_name 会组织为一个SQL语句
        result ret(db.query<all_name>());
        // 多表查询后对数据的操作
        for (auto it = ret.begin(); it != ret.end(); ++it)
        {
            std::cout<<it->name<<std::endl;
        }
        // 4.提交事务
        transaction.commit();
    }
    catch (std::exception &e)
    {
        std::cout << "多表查询出现异常，原因[" << e.what() << "]" << std::endl;
    }
}

// 根据学生姓名来获取学生
Student select_student_name(odb::mysql::database &db, const std::string &stu_name)
{
    try
    {
        // 3.获取事务对象，开启事务
        odb::transaction transaction(db.begin());
        using query = odb::query<Student>;
        using result = odb::result<Student>;
        // query::name == stu_name 会组织为一个SQL语句
        result ret(db.query<Student>(query::name == stu_name));
        if (ret.size() != 1)
        {
            std::cout << "数据量错误" << std::endl;
            return Student();
        }
        // 获取查询到的学生对象
        Student stu;
        stu = *ret.begin();
        // 4.提交事务
        transaction.commit();
        return stu;
    }
    catch (std::exception &e)
    {
        std::cout << "查询学生数据出现异常，原因[" << e.what() << "]" << std::endl;
        return Student();
    }
}
// 根据学号来查询
Student select_student_sn(odb::mysql::database &db, const unsigned long &sn)
{
    try
    {
        // 3.获取事务对象，开启事务
        odb::transaction transaction(db.begin());
        using query = odb::query<Student>;
        using result = odb::result<Student>;
        // query::name == stu_name 会组织为一个SQL语句
        result ret(db.query<Student>(query::sn == sn));
        if (ret.size() != 1)
        {
            std::cout << "数据量错误" << std::endl;
            return Student();
        }
        // 获取查询到的学生对象
        Student stu;
        stu = *ret.begin();
        // 4.提交事务
        transaction.commit();
        return stu;
    }
    catch (std::exception &e)
    {
        std::cout << "查询学生数据出现异常，原因[" << e.what() << "]" << std::endl;
        return Student();
    }
}

// 通过学生对象来进行更新
void update_student(odb::mysql::database &db, const Student &stu)
{
    try
    {
        // 3.获取事务对象，开启事务
        odb::transaction transaction(db.begin());
        db.update(stu);
        // 4.提交事务
        transaction.commit();
    }
    catch (std::exception &e)
    {
        std::cout << "更新学生数据出现异常，原因[" << e.what() << "]" << std::endl;
    }
}
// 根据学号删除学生
void erase_student(odb::mysql::database &db, const unsigned long &sn)
{
    try
    {
        // 3.获取事务对象，开启事务
        odb::transaction transaction(db.begin());
        using query = odb::query<Student>;
        db.erase_query<Student>(query::sn == sn);
        // 4.提交事务
        transaction.commit();
    }
    catch (std::exception &e)
    {
        std::cout << "更新学生数据出现异常，原因[" << e.what() << "]" << std::endl;
    }
}

int main(int argc, char *argv[])
{
    google::ParseCommandLineFlags(&argc, &argv, true);
    // 1.构造连接池工厂对象---只能用并且必须用unique_ptr
    std::unique_ptr<odb::mysql::connection_pool_factory> cpf(new odb::mysql::connection_pool_factory(FLAGS_max_num, 0));
    // 2.构造数据库操作对象
    odb::mysql::database db(FLAGS_user, FLAGS_password, FLAGS_db, FLAGS_host, FLAGS_port, "", FLAGS_cest, 0, std::move(cpf));
    // 3.获取事务对象，开启事务

    // 4.数据库进行操作

    // 5.关闭事务

    // insert_class(db);
    // insert_student(db);
    // auto stu = select_student_name(db, "钟");
    // stu.set_classes_id(2);
    // update_student(db, stu);
    // erase_student(db, 2023044026);
    // select_class_student(db, "高三一班");
    // select_class_student(db, "高三二班");
}